﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PatientsManagement.Database.Doctors;
using System.Windows.Forms;

namespace PatientsManagement.Database
{
    static class DoctorsData
    {
        static DoctorsDataContext doctors = new DoctorsDataContext();

        public class DoctorsInformation
        {
            public int ID { get; set; }
            public string Imię { get; set; }
            public string Nazwisko { get; set; }
            public string Miasto { get; set; }
            public string Ulica { get; set; }
            public string NrDomu { get; set; }
            public string Kod_Pocztowy { get; set; }
            public string Pesel { get; set; }
            public string Specjalizacja { get; set; }
        }

        public static IQueryable<DoctorsInformation> GetDoctorsTable()
        {
            var query = (from doctor in doctors.Doctors
                         join specializationNames in doctors.Specializations on doctor.SpecializationID equals specializationNames.SpecializationID
                         select new DoctorsInformation()
                         {
                             ID = doctor.DoctorID,
                             Imię = doctor.Forename,
                             Nazwisko = doctor.Surname,
                             Miasto = doctor.City,
                             Ulica = doctor.Street,
                             NrDomu = doctor.HouseNumber,
                             Kod_Pocztowy = doctor.PostalCode,
                             Pesel = doctor.Pesel,
                             Specjalizacja = specializationNames.Name
                         });
            return query;
        }

        public class DoctorsShortInformation
        {
            public int ID { get; set; }
            public string Imię { get; set; }
            public string Nazwisko { get; set; }
            public string Pesel { get; set; }
        }

        public static IQueryable<DoctorsShortInformation> GetShortenedDoctorsTable()
        {
            var query = (from doctor in doctors.Doctors
                         select new DoctorsShortInformation()
                         {
                             ID = doctor.DoctorID,
                             Imię = doctor.Forename,
                             Nazwisko = doctor.Surname,
                             Pesel = doctor.Pesel
                         });
            return query;
        }

        public static void SaveDoctorLink(int doctorID, int userID)
        {
            var query = (from doctorAccount in doctors.DoctorAccounts
                         join users in doctors.Users on doctorAccount.UserID equals users.UserID
                         where doctorAccount.DoctorID == doctorID
                         select doctorAccount).ToList();
            if (query.Count == 0)
            {
                Doctors.DoctorAccount account = new Doctors.DoctorAccount
                {
                    DoctorID = doctorID,
                    UserID = userID
                };
                doctors.DoctorAccounts.InsertOnSubmit(account);
                var singleUser = (from user in doctors.Users
                                  where user.UserID == userID
                             select user).Single();
                singleUser.Linked = true;
            }
            else
            {
                var singleAccount = (from doctorAccount in doctors.DoctorAccounts
                                     join users in doctors.Users on doctorAccount.UserID equals users.UserID
                             where doctorAccount.UserID == userID
                             select doctorAccount).Single();
                singleAccount.DoctorID = doctorID;
            }
            try
            {
                doctors.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public static void DeleteDoctorLink(int userID)
        {
            var singleLink = (from doctorAccount in doctors.DoctorAccounts
                              join users in doctors.Users on doctorAccount.UserID equals users.UserID
                             where doctorAccount.UserID == userID
                                select doctorAccount).Single();
            doctors.DoctorAccounts.DeleteOnSubmit(singleLink);
            var singleUser = (from user in doctors.Users
                              where user.UserID == userID
                              select user).Single();
            singleUser.Linked = false;
            try
            {
                doctors.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public static int GetDoctorID(int userID)
        {
            var query = (from doctorAccount in doctors.DoctorAccounts
                         where doctorAccount.UserID == userID
                         select doctorAccount).Single();
            return query.DoctorID;
        }

        public static bool LinkExists(int userID)
        {
            var query = (from doctorAccount in doctors.DoctorAccounts
                         join users in doctors.Users on doctorAccount.UserID equals users.UserID
                         where doctorAccount.UserID == userID
                         select doctorAccount).ToList();
            return (query.Count > 0);
        }

        public class DiagnosisInformation
        {
            public int ID_Diagnozy { get; set; }
            public int ID_Wizyty { get; set; }
            public string Pacjent { get; set; }
            public string Lekarz { get; set; }
            public string Nazwa_Diagnozy { get; set; }
            public string Data_Wizyty { get; set; }
        }

        public static IQueryable<DiagnosisInformation> GetDiagnosisTable()
        {
            var query = (from diagnosis in doctors.DiagnosisDatas
                         join names in doctors.Diagnosis on diagnosis.DiagnosisID equals names.DiagnosisID
                         join visit in doctors.Visits on diagnosis.VisitID equals visit.VisitID
                         join patient in doctors.Patients on visit.PatientID equals patient.PatientID into pd
                         join doctor in doctors.Doctors on visit.DoctorID equals doctor.DoctorID into dd
                         from patientsData in pd.DefaultIfEmpty()
                         from doctorData in dd.DefaultIfEmpty()
                         select new DiagnosisInformation()
                         {
                             ID_Diagnozy = diagnosis.DiagnosisID,
                             ID_Wizyty = visit.VisitID,
                             Nazwa_Diagnozy = names.DiagnosisName,
                             Pacjent = patientsData.Forename + " " + patientsData.Surname,
                             Lekarz = doctorData.Forename + " " + doctorData.Surname,
                             Data_Wizyty = visit.VisitDate.ToShortDateString()
                         });
            return query;
        }

        internal static List<string> DoctorSpecializations()
        {
            List<string> specializations;
            specializations = (from specialization in doctors.Specializations
                     select specialization.Name).ToList<string>();
            return specializations;
        }

        internal static void AddDoctor(string forename, string surname, string homeTown, string street, string houseNumber, string postalCode,string pesel, string specializationName)
        {
            Doctors.Doctor doctor = new Doctors.Doctor
            {
                Forename = forename,
                Surname = surname,
                City = homeTown,
                Street = street,
                HouseNumber = houseNumber,
                PostalCode = postalCode,
                Pesel = pesel,
                SpecializationID = GetSpecializationID(specializationName)
            };
            doctors.Doctors.InsertOnSubmit(doctor);
            try
            {
                doctors.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        private static int GetSpecializationID(string specializationName)
        {
            return (from specialization in doctors.Specializations
                    where specialization.Name == specializationName
                    select specialization.SpecializationID).Single();
        }

        internal static void EditDoctor(string doctorID, string forename,string surname, string homeTown, string street, 
            string houseNumber, string postalCode, string pesel, string specializationName)
        {
            var singleDoctor = (from doctor in doctors.Doctors
                                 where doctor.DoctorID == int.Parse(doctorID)
                                 select doctor).Single();
            singleDoctor.Forename = forename;
            singleDoctor.Surname = surname;
            singleDoctor.City = homeTown;
            singleDoctor.Street = street;
            singleDoctor.HouseNumber = houseNumber;
            singleDoctor.PostalCode = postalCode;
            singleDoctor.Pesel = pesel;
            singleDoctor.SpecializationID = GetSpecializationID(specializationName);
            try
            {
                doctors.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        internal static void DeleteDoctor(string doctorID)
        {
            var singleDoctor = (from doctor in doctors.Doctors
                                where doctor.DoctorID == int.Parse(doctorID)
                                select doctor).Single();
            doctors.Doctors.DeleteOnSubmit(singleDoctor);
            try
            {
                doctors.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public class PrescriptionsInformation
        {
            public int ID_Recepty { get; set; }
            public int ID_Wizyty { get; set; }
            public string Pacjent { get; set; }
            public string Lekarz { get; set; }
            public string Data_Wizyty { get; set; }
        }
        internal static IQueryable<PrescriptionsInformation> GetPrescriptionsTable()
        {
            var query = (from prescription in doctors.Prescriptions
                         join visit in doctors.Visits on prescription.VisitID equals visit.VisitID
                         join patient in doctors.Patients on visit.PatientID equals patient.PatientID into pd
                         join doctor in doctors.Doctors on visit.DoctorID equals doctor.DoctorID into dd
                         from patientsData in pd.DefaultIfEmpty()
                         from doctorData in dd.DefaultIfEmpty()
                         select new PrescriptionsInformation()
                         {
                             ID_Recepty = prescription.PrescriptionID,
                             ID_Wizyty = visit.VisitID,
                             Pacjent = patientsData.Forename + " " + patientsData.Surname,
                             Lekarz = doctorData.Forename + " " + doctorData.Surname,
                             Data_Wizyty = visit.VisitDate.ToShortDateString()
                         });
            return query;
        }

        public class VisitsInformation
        {
            public int ID_Wizyty { get; set; }
            public string Lekarz { get; set; }
            public string Pacjent { get; set; }
            public string Data_Wizyty { get; set; }
        }

        public static IQueryable<VisitsInformation> GetDoctorVisitsTable()
        {
            var query = (from visit in doctors.Visits
                         join patient in doctors.Patients on visit.PatientID equals patient.PatientID into patientData
                         join doctor in doctors.Doctors on visit.DoctorID equals doctor.DoctorID into doctorData
                         from pd in patientData.DefaultIfEmpty()
                         from dd in doctorData.DefaultIfEmpty()
                         select new VisitsInformation()
                         {
                             ID_Wizyty = visit.VisitID,
                             Lekarz = dd.Forename + " " + dd.Surname,
                             Pacjent = (pd.Forename + " " + pd.Surname) == null ? "" : (pd.Forename + " " + pd.Surname),
                             Data_Wizyty = visit.VisitDate.ToShortDateString()
                         });
            return query;
        }
    }
}
